-- 智慧医疗平台数据库初始化脚本
-- 包含测试数据和基础数据

USE [MediPlatform]
GO

-- 1. 清空现有数据（可选）
-- DELETE FROM [dbo].[RefundApplications]
-- DELETE FROM [dbo].[PrescriptionOrders]
-- DELETE FROM [dbo].[MedicalOrders]
-- DELETE FROM [dbo].[ConsultationOrders]
-- DELETE FROM [dbo].[RegistrationOrders]
-- DELETE FROM [dbo].[Users]

-- 2. 插入用户测试数据
INSERT INTO [dbo].[Users] ([Id], [Username], [Password], [Email], [PhoneNumber], [Role], [CreatedAt], [UpdatedAt])
VALUES 
    ('U001', 'doctor001', 'password123', 'doctor001@hospital.com', '13800138001', 'Doctor', GETDATE(), GETDATE()),
    ('U002', 'doctor002', 'password123', 'doctor002@hospital.com', '13800138002', 'Doctor', GETDATE(), GETDATE()),
    ('U003', 'nurse001', 'password123', 'nurse001@hospital.com', '13800138003', 'Nurse', GETDATE(), GETDATE()),
    ('U004', 'patient001', 'password123', 'patient001@email.com', '13800138004', 'Patient', GETDATE(), GETDATE()),
    ('U005', 'patient002', 'password123', 'patient002@email.com', '13800138005', 'Patient', GETDATE(), GETDATE()),
    ('U006', 'admin001', 'password123', 'admin001@hospital.com', '13800138006', 'Admin', GETDATE(), GETDATE());

-- 3. 插入科室测试数据
INSERT INTO [dbo].[Departments] ([Id], [Name], [Description], [CreatedAt], [UpdatedAt])
VALUES 
    ('DEPT001', '心内科', '心血管疾病诊断和治疗', GETDATE(), GETDATE()),
    ('DEPT002', '神经内科', '神经系统疾病诊断和治疗', GETDATE(), GETDATE()),
    ('DEPT003', '普外科', '普通外科手术和治疗', GETDATE(), GETDATE()),
    ('DEPT004', '呼吸内科', '呼吸系统疾病诊断和治疗', GETDATE(), GETDATE()),
    ('DEPT005', '内科', '内科疾病诊断和治疗', GETDATE(), GETDATE()),
    ('DEPT006', '急诊科', '急诊救治和分诊', GETDATE(), GETDATE());

-- 4. 插入医生测试数据
INSERT INTO [dbo].[Doctors] ([Id], [UserId], [DepartmentId], [Name], [Title], [Specialty], [CreatedAt], [UpdatedAt])
VALUES 
    ('D001', 'U001', 'DEPT001', '张医生', '主任医师', '心血管疾病', GETDATE(), GETDATE()),
    ('D002', 'U002', 'DEPT002', '李医生', '副主任医师', '神经系统疾病', GETDATE(), GETDATE()),
    ('D003', 'U003', 'DEPT003', '王医生', '主治医师', '普通外科', GETDATE(), GETDATE());

-- 5. 插入患者测试数据
INSERT INTO [dbo].[Patients] ([Id], [UserId], [Name], [Age], [Gender], [MedicalHistory], [CreatedAt], [UpdatedAt])
VALUES 
    ('P001', 'U004', '张三', 65, 'Male', '高血压病史5年，糖尿病病史3年', GETDATE(), GETDATE()),
    ('P002', 'U005', '李四', 72, 'Female', '高血压病史8年，房颤病史2年', GETDATE(), GETDATE());

-- 6. 插入挂号订单测试数据
INSERT INTO [dbo].[RegistrationOrders] ([Id], [PatientId], [DepartmentId], [DoctorId], [AppointmentDate], [AppointmentTime], [RegistrationFee], [Status], [CreatedAt], [UpdatedAt])
VALUES 
    ('RO001', 'P001', 'DEPT001', 'D001', '2025-01-20', '上午', 50.00, 'Confirmed', GETDATE(), GETDATE()),
    ('RO002', 'P002', 'DEPT002', 'D002', '2025-01-21', '下午', 50.00, 'Confirmed', GETDATE(), GETDATE()),
    ('RO003', 'P001', 'DEPT003', 'D003', '2025-01-22', '上午', 50.00, 'Pending', GETDATE(), GETDATE());

-- 7. 插入咨询订单测试数据
INSERT INTO [dbo].[ConsultationOrders] ([Id], [PatientId], [DoctorId], [ConsultationType], [SymptomDescription], [AppointmentTime], [UrgencyLevel], [Status], [CreatedAt], [UpdatedAt])
VALUES 
    ('CO001', 'P001', 'D001', '在线咨询', '头痛，伴有恶心', '2025-01-20T14:00:00Z', '一般', 'Confirmed', GETDATE(), GETDATE()),
    ('CO002', 'P002', 'D002', '在线咨询', '胸痛，伴有胸闷', '2025-01-21T15:00:00Z', '紧急', 'Confirmed', GETDATE(), GETDATE()),
    ('CO003', 'P001', 'D003', '在线咨询', '腹痛，伴有发热', '2025-01-22T16:00:00Z', '一般', 'Pending', GETDATE(), GETDATE());

-- 8. 插入医疗订单测试数据
INSERT INTO [dbo].[MedicalOrders] ([Id], [PatientId], [DoctorId], [Diagnosis], [TreatmentPlan], [MedicalAdvice], [OrderType], [Status], [CreatedAt], [UpdatedAt])
VALUES 
    ('MO001', 'P001', 'D001', '偏头痛', '布洛芬 400mg 每日3次', '避免熬夜，保持充足睡眠', '处方药', 'Issued', GETDATE(), GETDATE()),
    ('MO002', 'P002', 'D002', '高血压', '硝苯地平 10mg 每日2次', '低盐饮食，定期监测血压', '处方药', 'Issued', GETDATE(), GETDATE()),
    ('MO003', 'P001', 'D003', '急性阑尾炎', '手术治疗', '禁食，准备手术', '手术', 'Pending', GETDATE(), GETDATE());

-- 9. 插入处方订单测试数据
INSERT INTO [dbo].[PrescriptionOrders] ([Id], [PatientId], [DoctorId], [TotalAmount], [PaymentMethod], [Status], [CreatedAt], [UpdatedAt])
VALUES 
    ('PO001', 'P001', 'D001', 705.00, '医保卡', 'Paid', GETDATE(), GETDATE()),
    ('PO002', 'P002', 'D002', 450.00, '现金', 'Paid', GETDATE(), GETDATE()),
    ('PO003', 'P001', 'D003', 1200.00, '医保卡', 'Pending', GETDATE(), GETDATE());

-- 10. 插入处方药品明细测试数据
INSERT INTO [dbo].[PrescriptionItems] ([Id], [PrescriptionOrderId], [MedicineName], [Specification], [Quantity], [Usage], [UnitPrice], [CreatedAt], [UpdatedAt])
VALUES 
    ('PI001', 'PO001', '布洛芬', '400mg', 30, '每日3次，每次1片', 15.50, GETDATE(), GETDATE()),
    ('PI002', 'PO001', '维生素B1', '10mg', 60, '每日2次，每次1片', 8.00, GETDATE(), GETDATE()),
    ('PI003', 'PO002', '硝苯地平', '10mg', 30, '每日2次，每次1片', 15.00, GETDATE(), GETDATE()),
    ('PI004', 'PO003', '头孢曲松', '1g', 10, '每日1次，每次1支', 120.00, GETDATE(), GETDATE());

-- 11. 插入退款申请测试数据
INSERT INTO [dbo].[RefundApplications] ([Id], [OrderId], [PatientId], [RefundReason], [RefundAmount], [ApplicationTime], [Status], [CreatedAt], [UpdatedAt])
VALUES 
    ('RA001', 'PO001', 'P001', '药品过敏', 705.00, '2025-01-16T10:00:00Z', 'Pending', GETDATE(), GETDATE()),
    ('RA002', 'PO002', 'P002', '药品过期', 450.00, '2025-01-17T11:00:00Z', 'Approved', GETDATE(), GETDATE()),
    ('RA003', 'PO003', 'P001', '重复开药', 1200.00, '2025-01-18T12:00:00Z', 'Rejected', GETDATE(), GETDATE());

-- 12. 插入咨询记录测试数据
INSERT INTO [dbo].[ConsultationRecords] ([Id], [ConsultationOrderId], [ConsultationTime], [ConsultationContent], [DoctorAdvice], [CreatedAt], [UpdatedAt])
VALUES 
    ('CR001', 'CO001', '2025-01-20T14:00:00Z', '患者主诉头痛，伴有恶心，无其他不适', '建议服用布洛芬，避免熬夜', GETDATE(), GETDATE()),
    ('CR002', 'CO002', '2025-01-21T15:00:00Z', '患者主诉胸痛，伴有胸闷，需要进一步检查', '建议做心电图检查，排除心脏问题', GETDATE(), GETDATE());

-- 13. 插入咨询评价测试数据
INSERT INTO [dbo].[ConsultationReviews] ([Id], [ConsultationOrderId], [Rating], [ReviewContent], [ReviewTime], [CreatedAt], [UpdatedAt])
VALUES 
    ('CRev001', 'CO001', 5, '医生很专业，态度很好', '2025-01-20T15:00:00Z', GETDATE(), GETDATE()),
    ('CRev002', 'CO002', 4, '医生解答详细，建议很有帮助', '2025-01-21T16:00:00Z', GETDATE(), GETDATE());

-- 14. 插入系统配置测试数据
INSERT INTO [dbo].[SystemConfigs] ([Id], [ConfigKey], [ConfigValue], [Description], [CreatedAt], [UpdatedAt])
VALUES 
    ('SC001', 'MaxConsultationDuration', '30', '最大咨询时长（分钟）', GETDATE(), GETDATE()),
    ('SC002', 'RegistrationFee', '50', '挂号费（元）', GETDATE(), GETDATE()),
    ('SC003', 'RefundTimeLimit', '7', '退款时限（天）', GETDATE(), GETDATE()),
    ('SC004', 'MaxPrescriptionItems', '10', '单次处方最大药品数量', GETDATE(), GETDATE());

-- 15. 插入日志测试数据
INSERT INTO [dbo].[SystemLogs] ([Id], [UserId], [Action], [Description], [IpAddress], [LogTime], [CreatedAt])
VALUES 
    ('SL001', 'U001', 'Login', '用户登录', '192.168.1.100', GETDATE(), GETDATE()),
    ('SL002', 'U004', 'CreateOrder', '创建挂号订单', '192.168.1.101', GETDATE(), GETDATE()),
    ('SL003', 'U001', 'CreatePrescription', '开具处方', '192.168.1.100', GETDATE(), GETDATE()),
    ('SL004', 'U004', 'ApplyRefund', '申请退款', '192.168.1.101', GETDATE(), GETDATE());

-- 16. 验证数据插入
SELECT 'Users' as TableName, COUNT(*) as RecordCount FROM [dbo].[Users]
UNION ALL
SELECT 'Departments', COUNT(*) FROM [dbo].[Departments]
UNION ALL
SELECT 'Doctors', COUNT(*) FROM [dbo].[Doctors]
UNION ALL
SELECT 'Patients', COUNT(*) FROM [dbo].[Patients]
UNION ALL
SELECT 'RegistrationOrders', COUNT(*) FROM [dbo].[RegistrationOrders]
UNION ALL
SELECT 'ConsultationOrders', COUNT(*) FROM [dbo].[ConsultationOrders]
UNION ALL
SELECT 'MedicalOrders', COUNT(*) FROM [dbo].[MedicalOrders]
UNION ALL
SELECT 'PrescriptionOrders', COUNT(*) FROM [dbo].[PrescriptionOrders]
UNION ALL
SELECT 'PrescriptionItems', COUNT(*) FROM [dbo].[PrescriptionItems]
UNION ALL
SELECT 'RefundApplications', COUNT(*) FROM [dbo].[RefundApplications]
UNION ALL
SELECT 'ConsultationRecords', COUNT(*) FROM [dbo].[ConsultationRecords]
UNION ALL
SELECT 'ConsultationReviews', COUNT(*) FROM [dbo].[ConsultationReviews]
UNION ALL
SELECT 'SystemConfigs', COUNT(*) FROM [dbo].[SystemConfigs]
UNION ALL
SELECT 'SystemLogs', COUNT(*) FROM [dbo].[SystemLogs];

-- 17. 创建测试查询视图
GO

CREATE VIEW [dbo].[vw_TestDataSummary] AS
SELECT 
    '用户统计' as Category,
    COUNT(*) as Count,
    'Users' as TableName
FROM [dbo].[Users]
UNION ALL
SELECT 
    '医生统计',
    COUNT(*),
    'Doctors'
FROM [dbo].[Doctors]
UNION ALL
SELECT 
    '患者统计',
    COUNT(*),
    'Patients'
FROM [dbo].[Patients]
UNION ALL
SELECT 
    '挂号订单统计',
    COUNT(*),
    'RegistrationOrders'
FROM [dbo].[RegistrationOrders]
UNION ALL
SELECT 
    '咨询订单统计',
    COUNT(*),
    'ConsultationOrders'
FROM [dbo].[ConsultationOrders]
UNION ALL
SELECT 
    '医疗订单统计',
    COUNT(*),
    'MedicalOrders'
FROM [dbo].[MedicalOrders]
UNION ALL
SELECT 
    '处方订单统计',
    COUNT(*),
    'PrescriptionOrders'
FROM [dbo].[PrescriptionOrders]
UNION ALL
SELECT 
    '退款申请统计',
    COUNT(*),
    'RefundApplications'
FROM [dbo].[RefundApplications];

GO

-- 18. 创建测试数据查询存储过程
CREATE PROCEDURE [dbo].[sp_GetTestDataSummary]
AS
BEGIN
    SELECT * FROM [dbo].[vw_TestDataSummary]
    ORDER BY Category;
END

GO

-- 19. 创建测试数据清理存储过程
CREATE PROCEDURE [dbo].[sp_ClearTestData]
AS
BEGIN
    BEGIN TRANSACTION;
    
    DELETE FROM [dbo].[SystemLogs];
    DELETE FROM [dbo].[ConsultationReviews];
    DELETE FROM [dbo].[ConsultationRecords];
    DELETE FROM [dbo].[RefundApplications];
    DELETE FROM [dbo].[PrescriptionItems];
    DELETE FROM [dbo].[PrescriptionOrders];
    DELETE FROM [dbo].[MedicalOrders];
    DELETE FROM [dbo].[ConsultationOrders];
    DELETE FROM [dbo].[RegistrationOrders];
    DELETE FROM [dbo].[Patients];
    DELETE FROM [dbo].[Doctors];
    DELETE FROM [dbo].[Departments];
    DELETE FROM [dbo].[Users];
    
    COMMIT TRANSACTION;
    
    PRINT '测试数据已清理完成';
END

GO

-- 20. 执行测试数据摘要查询
EXEC [dbo].[sp_GetTestDataSummary];

PRINT '数据库初始化完成！';
PRINT '测试数据已成功插入到所有相关表中。';
PRINT '可以使用以下存储过程：';
PRINT '1. EXEC [dbo].[sp_GetTestDataSummary] - 查看测试数据摘要';
PRINT '2. EXEC [dbo].[sp_ClearTestData] - 清理所有测试数据'; 